﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Transactions;
using System.Web;
using Eacademy.Domain.Abstractions;
using Eacademy.Domain.Entities;
using Eacademy.Domain.Models;
using Eacademy.Library.Helpers;
using Elmah;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Text;
using System.Data.Common;
using System.Data;
using SubSonic.DataProviders;
using System.Drawing;
using Eacademy.Library.Membership;
using System.Web.Security;

namespace Eacademy.Domain.Repository
{

    public class MentorRepository : IMentorRepository
    {
        EacademyDB db = new EacademyDB();
        private static CustomMembershipProvider Provider
        {
            get
            {
                return (CustomMembershipProvider)Membership.Provider;
            }
        }
        public User AddMentor(AdminMentorsViewModel adminMentorsViewModel)
        {
            try
            {

                User user = new User();
                using (TransactionScope ts = new TransactionScope())
                {
                    using (SharedDbConnectionScope scs = new SharedDbConnectionScope())
                    {
                        //User
                        user.UserPrimaryEmail = adminMentorsViewModel.PrimaryEmail;
                        user.UserAlternateEmail = adminMentorsViewModel.SecondaryEmail;
                        user.Username = adminMentorsViewModel.Username;
                        user.PasswordSalt = KeyGenerator.GenerateRandomKey(15, 15, true, true, true);
                        user.Password = PasswordHelper.ComputeHash(adminMentorsViewModel.Password, user.PasswordSalt);
                        //user.Password = adminUsersViewModel.Password;
                        user.LastUserActivity = DateTime.Now;
                        user.ThumbImageName = adminMentorsViewModel.ThumbImageName;

                        Mentor mentor = new Mentor();
                        user.UserTypeId = UserTypes.Mentor;
                        user.Save();
                        mentor.UserId = user.UserId.ToInteger();
                        mentor.EmployeeNumber = adminMentorsViewModel.EmployeeNumber;
                        mentor.Firstname = adminMentorsViewModel.FirstName;
                        mentor.Middlename = adminMentorsViewModel.MiddleName;
                        mentor.Lastname = adminMentorsViewModel.LastName;
                        mentor.Save();

                    }
                    ts.Complete();
                }

                return user;
            }
            catch (Exception ex)
            { }
            return null;
        }

        public Boolean UpdateMentor(AdminMentorsViewModel adminMentorsViewModel)
        {
            try
            {
                using (TransactionScope ts = new TransactionScope())
                {
                    using (SharedDbConnectionScope scs = new SharedDbConnectionScope())
                    {


                        Mentor mentor = db.Select.From<Mentor>().Where(MentorsTable.MentorIdColumn)
                            .IsEqualTo(adminMentorsViewModel.MentorId).ExecuteSingle<Mentor>();
                        mentor.EmployeeNumber = adminMentorsViewModel.EmployeeNumber;
                        mentor.Firstname = adminMentorsViewModel.FirstName;
                        mentor.Middlename = adminMentorsViewModel.MiddleName;
                        mentor.Lastname = adminMentorsViewModel.LastName;
 
                        mentor.Update();

                        User user = db.Select.From<User>().Where(UsersTable.UserIdColumn)
                            .IsEqualTo(mentor.UserId).ExecuteSingle<User>();
                        //User
                        user.UserPrimaryEmail = adminMentorsViewModel.PrimaryEmail;
                        user.UserAlternateEmail = adminMentorsViewModel.SecondaryEmail;
                        user.Username = adminMentorsViewModel.Username;
                        //user.PasswordSalt = KeyGenerator.GenerateRandomKey(15, 15, true, true, true);
                        //user.Password = PasswordHelper.ComputeHash(adminMentorsViewModel.Password, user.PasswordSalt);
                        //user.LastUserActivity = DateTime.Now;
                        //user.UserTypeId = UserTypes.Mentor;
                        user.ThumbImageName = adminMentorsViewModel.ThumbImageName;

                        user.Update();

                    }
                    ts.Complete();
                }

                return true;
            }
            catch (Exception ex)
            { }
            return false;
        }

        public List<MentorCompactViewModel> GetMentors()
        {
            int totalcount = 0;
            return GetMentors(1, 0, new MentorFilterViewModel(), out totalcount, false);
        }

        public List<MentorCompactViewModel> GetMentors(MentorFilterViewModel mentorFilterViewModel)
        {
            int totalcount = 0;
            return GetMentors(1, 0, mentorFilterViewModel, out totalcount, false);
        }

        public List<MentorCompactViewModel> GetMentors(int pageIndex, int pageSize, MentorFilterViewModel parameters, out int totalCount, bool count = true)
        {

            MentorFilterViewModel searchparameter = parameters;
            totalCount = 0;
            DbFactory factory = new DbFactory();
            Database db = factory.database;
            List<MentorCompactViewModel> mentorViewList = new List<MentorCompactViewModel>();
            try
            {
                StringBuilder sql = new StringBuilder();
                sql.AddStartRemove();
                sql.AppendLine(@"SELECT *  ");
                sql.AddEndRemove();
                sql.AppendLine(@"FROM mentors m ");

                sql.Append(" WHERE 1 = 1 ");

                if (!String.IsNullOrEmpty(parameters.Name))
                {
                    sql.AppendFormat(" AND ( firstname LIKE '{0}%' OR middlename LIKE '{0}%' OR lastname LIKE '{0}%' ) ", parameters.Name);
                }

                if (pageSize > 0)
                {
                    //control page size
                    pageSize = pageSize > 10 ? 10 : pageSize;

                    sql.AddStartRemove();
                    sql.Append(" LIMIT ?StartIndex, ?MaxRecord ");
                    sql.AddEndRemove();
                }

                DbCommand comm = db.GetSqlStringCommand(sql.ToString());
                db.AddInParameter(comm, "?StartIndex", DbType.Int32, (pageIndex - 1) * pageSize);
                db.AddInParameter(comm, "?MaxRecord", DbType.Int32, pageSize);
                DataSet ds = factory.ExecuteQuery(comm);

                DataTable mentorsTable = ds.Tables[0];


                mentorViewList = mentorsTable.AsEnumerable().Select(datarow => new MentorCompactViewModel
                {
                    FirstName = datarow["firstname"].ToString(),
                    MiddleName = datarow["middlename"].ToString(),
                    LastName = datarow["lastname"].ToString(),
                    EmployeeNumber = datarow["employee_number"].ToString(),
                    MentorId = datarow["mentor_id"].ToInteger(),
                    UserId = datarow["user_id"].ToInteger()
                }).ToList();


                if (count)
                {
                    string countQuery = sql.ToString().GenerateCountQuery();
                    comm.CommandText = countQuery;
                    ds = factory.ExecuteQuery(comm);
                    totalCount = ds.Tables[0].Rows[0]["count"].ToInteger();
                }

                return mentorViewList;
            }
            catch (Exception ex)
            { }
            return mentorViewList;
        }

        public bool DeleteMentor(int id)
        {
            try
            {
                MentorBase.Delete(x => x.MentorId == id);
                return true;
            }
            catch (Exception ex)
            {
                ErrorSignal.FromCurrentContext().Raise(ex);
            }
            return false;
        }

        public AdminMentorsViewModel GetMentorByMentorId(int id)
        {
            AdminMentorsViewModel adminMentorsViewModel = new AdminMentorsViewModel();
            try
            {


                using (TransactionScope ts = new TransactionScope())
                {
                    using (SharedDbConnectionScope scs = new SharedDbConnectionScope())
                    {
                        Mentor mentor = db.Select.From<Mentor>().Where(MentorsTable.MentorIdColumn)
                            .IsEqualTo(id).ExecuteSingle<Mentor>();

                        User user = db.Select.From<User>().Where(UsersTable.UserIdColumn)
                            .IsEqualTo(mentor.UserId).ExecuteSingle<User>();

                        adminMentorsViewModel.EmployeeNumber = mentor.EmployeeNumber;
                        adminMentorsViewModel.FirstName = mentor.Firstname;
                        adminMentorsViewModel.MiddleName = mentor.Middlename;
                        adminMentorsViewModel.LastName = mentor.Lastname;
                        adminMentorsViewModel.PrimaryEmail = user.UserPrimaryEmail;
                        adminMentorsViewModel.SecondaryEmail = user.UserAlternateEmail;
                        adminMentorsViewModel.Username = user.Username;
                        adminMentorsViewModel.UserTypeId = user.UserTypeId;
                        adminMentorsViewModel.MentorId = mentor.MentorId;
                        adminMentorsViewModel.ThumbImageName = user.ThumbImageName;
                    }
                    ts.Complete();
                }


            }
            catch (Exception ex)
            { }
            return adminMentorsViewModel;
        }

        public AdminMentorsViewModel GetMentorByUserId(int id)
        {
            AdminMentorsViewModel adminMentorsViewModel = new AdminMentorsViewModel();
            try
            {


                using (TransactionScope ts = new TransactionScope())
                {
                    using (SharedDbConnectionScope scs = new SharedDbConnectionScope())
                    {
                        Mentor mentor = db.Select.From<Mentor>().Where(MentorsTable.UserIdColumn)
                            .IsEqualTo(id).ExecuteSingle<Mentor>();

                        User user = db.Select.From<User>().Where(UsersTable.UserIdColumn)
                            .IsEqualTo(mentor.UserId).ExecuteSingle<User>();

                        adminMentorsViewModel.EmployeeNumber = mentor.EmployeeNumber;
                        adminMentorsViewModel.FirstName = mentor.Firstname;
                        adminMentorsViewModel.MiddleName = mentor.Middlename;
                        adminMentorsViewModel.LastName = mentor.Lastname;
                        adminMentorsViewModel.PrimaryEmail = user.UserPrimaryEmail;
                        adminMentorsViewModel.SecondaryEmail = user.UserAlternateEmail;
                        adminMentorsViewModel.Username = user.Username;
                        adminMentorsViewModel.UserTypeId = user.UserTypeId;
                        adminMentorsViewModel.MentorId = mentor.MentorId;
                        adminMentorsViewModel.ThumbImageName = user.ThumbImageName;
                    }
                    ts.Complete();
                }


            }
            catch (Exception ex)
            { }
            return adminMentorsViewModel;
        }

        public List<MentorCompactViewModel> GetMentorsBySubject(int id)
        {
            DbFactory factory = new DbFactory();
            Database db = factory.database;
            List<MentorCompactViewModel> mentorViewList = new List<MentorCompactViewModel>();
            try
            {
                StringBuilder sql = new StringBuilder();
                sql.AppendLine(@"SELECT m.*  ");
                sql.AppendLine(@"FROM mentors_subjects ms ");
                sql.AppendLine(@"LEFT JOIN mentors m ON m.mentor_id = ms.mentor_id ");
                sql.Append(" WHERE 1 = 1 ");

                if (id > 0)
                {
                    sql.AppendLine(" AND ms.subject_id = ?SubjectId  ");
                }

                DbCommand comm = db.GetSqlStringCommand(sql.ToString());
                db.AddInParameter(comm, "?SubjectId", DbType.Int32, id);
                DataSet ds = factory.ExecuteQuery(comm);

                DataTable mentorsTable = ds.Tables[0];


                mentorViewList = mentorsTable.AsEnumerable().Select(datarow => new MentorCompactViewModel
                {
                    FirstName = datarow["firstname"].ToString(),
                    MiddleName = datarow["middlename"].ToString(),
                    LastName = datarow["lastname"].ToString(),
                    EmployeeNumber = datarow["employee_number"].ToString(),
                    MentorId = datarow["mentor_id"].ToInteger(),
                    UserId =  datarow["user_id"].ToInteger()

                }).ToList();

                return mentorViewList;
            }
            catch (Exception ex)
            { }
            return mentorViewList;
        }

    }
}